﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace OestsDataBase
{
    public static class UserDAL
    {
        const string TABLE_NAME = "T_User";
        //学生
        public static void CreateTable()
        {
            string sqlCheck = "if object_id( 'T_User') is not null select 1 else select 0";
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreacte = @"CREATE TABLE T_User (id bigint identity(1,1) ,
															number NVARCHAR(50) primary key,
															name NVARCHAR(50) not null,
															password NVARCHAR(50),
															user_type varchar(10) not null,
															login_time datetime,
															logout_time datetime)";

                SqlHelper.ExecuteNonQuery(sqlCreacte);
            }
            //else
            //{

            //    string sql = "dbcc  checkident(T_Student,RESEED,0)";
            //    SqlHelper.ExecuteNonQuery(sql);
            //}
        }
        /// <summary>
        /// 添加一条数据返回自增列ID
        /// </summary>
        /// <param name="cls"></param>
        /// <returns></returns>
        public static int Add(User u)
        {
            //先将自增字段设置为从0开始
            //DBManager.Checkident(TABLE_NAME);
            string strSql = string.Format("insert into {0} (number,name, password,user_type,id_no) values('{1}','{2}','{3}','{4}','{5}');SELECT @@Identity",
                TABLE_NAME, u.Number, u.Name, u.Password, u.Type, u.IdNo);
            object obj = SqlHelper.ExecuteScalar(strSql);
            return Int32.Parse(obj.ToString());
        }

        public static User GetUser(string num)
        {
            string sql = "select * from " + TABLE_NAME + " where number = " + "'" + num + "'";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            if (dt.Rows.Count > 0)
                return ReadData(dt.Rows[0]);
            else return null;
        }
        public static List<User> GetUsers()
        {
            List<User> users = new List<User>();
            string sql = "select * from " + TABLE_NAME;
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                    users.Add(ReadData(row));
            }
            else
                return null;

            return users;
        }
        public static List<User> GetStudentData()
        {
            List<User> students = new List<User>();
            string sql = "select * from " + TABLE_NAME + " where user_type = " + "'" + UserType.Student.ToString() + "'";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                    students.Add(ReadData(row));
            }
            else
                return null;

            return students;
        }

        public static List<User> GetTeacherData()
        {
            List<User> students = new List<User>();
            string sql = "select * from " + TABLE_NAME + " where user_type = " + "'" + UserType.Teacher.ToString() + "'";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                    students.Add(ReadData(row));
            }
            else
                return null;

            return students;
        }


        private static User ReadData(DataRow dr)
        {
            User s = new User();
            s.DBid = Int32.Parse(dr["id"].ToString());
            s.Number = dr["number"].ToString();
            s.Name = dr["name"].ToString();
            s.IdNo = dr["id_no"].ToString();
            s.Password = dr["password"].Equals(DBNull.Value) ? "" : dr["password"].ToString();
            s.Type = (UserType)Enum.Parse(typeof(UserType), dr["user_type"].ToString());
            return s;
        }

        public static void SaveLoginTime(int id)
        {
            DateTime date = DateTime.Now;
            string strSql = string.Format("update T_User set login_time='{0}' where id = @id", date);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
        }

        public static void SaveLogoutTime(int id)
        {
            DateTime date = DateTime.Now;
            string strSql = string.Format("update T_User set logout_time='{0}' where id = @id", date);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
        }
        /// <summary>
        /// 删除一条用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteUser(int id)
        {
            string strSql = string.Format("delete from T_User where id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;

        }

        public static bool DeleteUsers(List<int> ids)
        {
            if (ids == null || ids.Count == 0) return true;
            string idStr = string.Join(",", ids);
            string strSql = string.Format($"delete from T_User where id in ({idStr})");
            int n = SqlHelper.ExecuteNonQuery(strSql);
            return n > 0;

        }



        public static bool ModifyUser(User user)
        {
            string strSql = string.Format("update T_User set number='{0}', name='{1}', id_no='{2}', password='{3}'  where id = @id", user.Number, user.Name, user.IdNo, user.Password);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", user.DBid));
            if (n != 0)
            {
                return true;
            }
            return true;
        }

        public static string GetPassword(int id)
        {
            string strSql = "select password from T_User where id=@id";
            object obj = SqlHelper.ExecuteScalar(strSql, new SqlParameter("@id", id));
            if (obj != null)
            {
                return obj.ToString();
            }
            return null;
        }


        public static User CheckExist(string number)
        {
            string strSql = "select * from T_User where number = @number";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql, new SqlParameter("@number", number));
            if (dt != null && dt.Rows.Count != 0)
            {
                User exsistUser = ReadData(dt.Rows[0]);
                return exsistUser;
                //if (exsistUser.DBid != user.DBid)
                //    return true;
            }
            return null;

            //object obj = SqlHelper.ExecuteScalar(strSql, new SqlParameter("@number", number));
            //return (int)obj; ;
        }

        public static bool CheckExist(User user)
        {
            string strSql = "select * from T_User where number= @number ";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql, new SqlParameter("@number", user.Number));
            if (dt != null && dt.Rows.Count != 0)
            {
                User exsistUser = ReadData(dt.Rows[0]);
                if (exsistUser.DBid != user.DBid)
                    return true;
            }
            return false;
        }
        public static bool ModifyPwd(string pwd, int id)
        {
            string strSql = string.Format("update T_User set password='{0}'  where id = @id", pwd);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return true;
        }

        /// <summary>
        /// addList：数据源，添加的原始数据
        /// repeatNumList：重复学号的数据
        /// 返回 bool
        /// </summary>
        /// <param name="addList"></param>
        /// <param name="repeatIdList"></param>
        /// <returns></returns>
        public static bool AddStudents(ref List<Student> addList)
        {
            bool res = true;
            if (addList != null && addList.Count > 0)
            {
                SqlConnection cn = new SqlConnection(SqlHelper.connStr);
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                // 打开数据库  
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }

                // 开始事务  
                transaction = cn.BeginTransaction();
                cmd.Transaction = transaction;
                cmd.Connection = cn;

                try
                {
                    for (int i = 0; i < addList.Count; i++)
                    {
                        var u = addList[i];
                        /*执行sql 语句*/
                        //添加学生信息的User表中
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = $"insert into {TABLE_NAME} (number,name, password,user_type,id_no) values('{u.Number}','{u.Name}','{u.Password}','{u.Type}','{u.IdNo}');SELECT @@Identity";
                        int newId = Int32.Parse(cmd.ExecuteScalar().ToString());
                        u.DBid = newId;//更新DBId
                        //添加学生班级表中
                        if (u.ClassIds != null && u.ClassIds.Count > 0)
                        {
                            cmd.CommandType = CommandType.Text;

                            string values = "";
                            foreach (var item in u.ClassIds)
                            {
                                values += $"({u.DBid},{item}),";
                            }
                            values = values.Substring(0, values.Length - 1);
                            cmd.CommandText = $"insert into T_StudentClass (student_id, class_id) values{values};";
                            int n = cmd.ExecuteNonQuery();
                        }


                    }
                    // 提交事务  
                    transaction.Commit();
                    res = true;
                }
                catch
                {
                    // 回滚事务  
                    transaction.Rollback();
                    res = false;
                }
                finally
                {
                    // 关闭数据库  
                    if (cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                    cn.Dispose();
                    cmd.Dispose();
                    transaction.Dispose();
                }
            }

            return res;
        }
    }
}
